Skip to main content

ACID Properties

A transaction in a database is a single logical unit of work that can consist of one or multiple SQL operations (like INSERT, UPDATE, DELETE, SELECT). Transactions are crucial because they ensure data consistency and reliability, even in cases of system failures or concurrent access.

The ACID acronym describes the key properties every reliable transaction must satisfy:

1. Atomicity (All or Nothing)​

  • Definition: A transaction must execute completely or not at all.
  • If any operation in the transaction fails, the entire transaction is rolled back.
  • Prevents partial updates that could corrupt data.

Example:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;  -- Withdraw from A
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;  -- Deposit to B

COMMIT;

If the first update succeeds but the second fails (e.g., due to a crash), Atomicity ensures the whole transaction is rolled back, so account A is not debited without B being credited.

2. Consistency​

  • Definition: A transaction should bring the database from one valid state to another valid state, preserving all defined rules, constraints, and relationships.
  • Ensures that data integrity constraints (like foreign keys, unique constraints) are not violated.

Example:

  • If a transfer transaction tries to move money from account A (balance = 500) to B, and A doesn’t have enough funds:
-- Withdraw 600 from A (only 500 available)

Consistency rules prevent A’s balance from becoming -100 if negative balances are not allowed.

3. Isolation​

  • Definition: Transactions should execute independently without interference, even when run concurrently.
  • Prevents problems like dirty reads, non-repeatable reads, and phantom reads.

Example:

Two users perform operations simultaneously:

  • Transaction 1: Withdraws money from account A.
  • Transaction 2: Reads account A’s balance.

Depending on the isolation level (READ COMMITTED, REPEATABLE READ, SERIALIZABLE), Transaction 2 may or may not see uncommitted changes. For strong isolation, Transaction 2 only sees committed balances.

4. Durability​

  • Definition: Once a transaction is committed, its changes are permanent, even if the system crashes afterward.
  • Achieved by writing to persistent storage (transaction logs, database files).

Example:

If you successfully transfer money and the system crashes right after COMMIT, the transaction log ensures that the change is still preserved when the system restarts.